﻿Imports System.Data.OleDb

Public Class FishGateway
    Inherits DataGateway

    Protected Overrides ReadOnly Property FindAllQuery As String
        Get
            Return "SELECT * FROM FishListView"
        End Get
    End Property

    Protected Overrides ReadOnly Property DeleteQuery As String
        Get
            Return "DELETE FROM Fish"
        End Get
    End Property

    Protected Overrides ReadOnly Property SiteUserQuery() As String
        Get
            Return "SELECT SiteUser.* " &
                   "FROM Fish LEFT JOIN SiteUser " &
                   "ON Fish.UserID=SiteUser.ID " &
                   "WHERE Fish.ID=@ID"
        End Get
    End Property

    Public Overridable Function FindNotInAquarium(ByVal aquariumID As ULong) As DataTable
        Dim sqlQuery As String = "SELECT * FROM FishListView " &
                                 "WHERE ID NOT IN (SELECT FishID FROM AquariumFish WHERE AquariumID=@AquariumID)"
        Dim queryParams As New OrderedDictionary()
        queryParams.Add("@AquariumID", aquariumID)
        Return QueryTable(sqlQuery, queryParams)
    End Function

    Public Function Insert(ByVal title As String, ByVal scientificName As String,
                           ByVal description As String, ByVal diet As String,
                           ByVal maxLength As Double, ByVal temperature As Double, ByVal pH As Double,
                           ByVal minTankSize As Double, ByVal userID As ULong, ByVal photoID As ULong) As ULong
        Dim newID As ULong
        Dim sqlQuery As String = "INSERT INTO Fish (Title, ScientificName, Description, PhotoID, UserID, MaxLength, Temperature, pH, MinTankSize, Diet)" &
                                 "VALUES (@Title, @ScientificName, @Description, @PhotoID, @UserID, @MaxLength, @Temperature, @pH, @MinTankSize, @Diet)"
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Title", title)
                command.Parameters.AddWithValue("@ScientificName", scientificName)
                command.Parameters.AddWithValue("@Description", description)
                command.Parameters.AddWithValue("@PhotoID", photoID)
                command.Parameters.AddWithValue("@UserID", userID)
                command.Parameters.AddWithValue("@MaxLength", maxLength)
                command.Parameters.AddWithValue("@Temperature", temperature)
                command.Parameters.AddWithValue("@pH", pH)
                command.Parameters.AddWithValue("@MinTankSize", minTankSize)
                command.Parameters.AddWithValue("@Diet", diet)
                connection.Open()
                command.ExecuteNonQuery()
                newID = GetLastInsertID(command)
            End Using
        End Using

        Return newID
    End Function

    Public Sub Update(ByVal fishID As ULong, ByVal title As String, ByVal scientificName As String,
                      ByVal description As String, ByVal diet As String,
                      ByVal maxLength As Double, ByVal temperature As Double, ByVal pH As Double,
                      ByVal minTankSize As Double, Optional ByVal photoID As ULong = 0)
        Dim sqlQuery As String
        Dim where As String = " WHERE ID=@FishID"
        If photoID = 0 Then
            sqlQuery = "UPDATE Fish SET Title=@Title, ScientificName=@ScientificName, Description=@Description, " &
                       "MaxLength=@MaxLength, Temperature=@Temperature, pH=@pH, MinTankSize=@MinTankSize, Diet=@Diet" & where
        Else
            sqlQuery = "UPDATE Fish SET Title=@Title, ScientificName=@ScientificName, Description=@Description, " &
                       "MaxLength=@MaxLength, Temperature=@Temperature, pH=@pH, MinTankSize=@MinTankSize, Diet=@Diet, " &
                       "PhotoID=@PhotoID" & where
        End If
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Title", title)
                command.Parameters.AddWithValue("@ScientificName", scientificName)
                command.Parameters.AddWithValue("@Description", description)
                command.Parameters.AddWithValue("@MaxLength", maxLength)
                command.Parameters.AddWithValue("@Temperature", temperature)
                command.Parameters.AddWithValue("@pH", pH)
                command.Parameters.AddWithValue("@MinTankSize", minTankSize)
                command.Parameters.AddWithValue("@Diet", diet)
                If photoID <> 0 Then
                    command.Parameters.AddWithValue("@PhotoID", photoID)
                End If
                command.Parameters.AddWithValue("@FishID", fishID)
                connection.Open()
                command.ExecuteNonQuery()
            End Using
        End Using
    End Sub
End Class
